This project explores Covid-19 global data (as of June 2023) using Microsoft SQL Server. It demonstrates skills such as Joins, Common Table Expression (CTE), Temp Tables, Windows Functions, Aggregate Functions, Creating Views, Converting Data Types, and other fundamental SQL functions to query relevant data and perform calculations. The results are later visualized in a dashboard created using Tableau.
data source: https://ourworldindata.org/covid-deaths
Microsoft SQL Server query file (.sql): https://github.com/lea-rulloda/Portfolio/blob/75d1b68a8621b1a68600c99120e906c8d6b54200/SQLQuery_Covid19_Data_Exploration.sql
Tableau Dashboard: https://public.tableau.com/views/Covid-19GlobalData_16868296829780/Dashboard1?:language=en-US&:display_count=n&:origin=viz_share_link
#Importing libraries
from sqlalchemy import create_engine
import pandas as pd
#Creating an SQLAlchemy engine
engine = create_engine('mssql+pyodbc://CLARK\SQLEXPRESS/Portfolio?trusted_connection=yes&driver=ODBC+Driver+17+for+SQL+Server')
#Executing SQL query and fetching the results into a DataFrame
#Querying Covid_Deaths table
query1 = '''
SELECT *
FROM Portfolio..Covid_Deaths
WHERE continent IS NOT NULL
ORDER BY 3, 4 DESC;
'''
df1 = pd.read_sql(query1, engine)
#Querying the DataFrame
df1
| iso_code | continent | location | date | population | total_cases | new_cases | new_cases_smoothed | total_deaths | new_deaths | ... | new_deaths_smoothed_per_million | reproduction_rate | icu_patients | icu_patients_per_million | hosp_patients | hosp_patients_per_million | weekly_icu_admissions | weekly_icu_admissions_per_million | weekly_hosp_admissions | weekly_hosp_admissions_per_million | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | AFG | Asia | Afghanistan | 2023-06-07 | 41128772.0 | 222074.0 | 0.0 | 54.714 | 7918 | 0.0 | ... | 0.010 | None | None | None | None | None | None | None | None | None |
| 1 | AFG | Asia | Afghanistan | 2023-06-06 | 41128772.0 | 222074.0 | 0.0 | 74.000 | 7918 | 0.0 | ... | 0.010 | None | None | None | None | None | None | None | None | None |
| 2 | AFG | Asia | Afghanistan | 2023-06-05 | 41128772.0 | 222074.0 | 82.0 | 94.429 | 7918 | 0.0 | ... | 0.014 | None | None | None | None | None | None | None | None | None |
| 3 | AFG | Asia | Afghanistan | 2023-06-04 | 41128772.0 | 221992.0 | 18.0 | 110.714 | 7918 | 0.0 | ... | 0.014 | None | None | None | None | None | None | None | None | None |
| 4 | AFG | Asia | Afghanistan | 2023-06-03 | 41128772.0 | 221974.0 | 108.0 | 110.000 | 7918 | 1.0 | ... | 0.014 | None | None | None | None | None | None | None | None | None |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 301593 | ZWE | Africa | Zimbabwe | 2020-01-07 | 16320539.0 | NaN | 0.0 | NaN | None | 0.0 | ... | NaN | None | None | None | None | None | None | None | None | None |
| 301594 | ZWE | Africa | Zimbabwe | 2020-01-06 | 16320539.0 | NaN | 0.0 | NaN | None | 0.0 | ... | NaN | None | None | None | None | None | None | None | None | None |
| 301595 | ZWE | Africa | Zimbabwe | 2020-01-05 | 16320539.0 | NaN | 0.0 | NaN | None | 0.0 | ... | NaN | None | None | None | None | None | None | None | None | None |
| 301596 | ZWE | Africa | Zimbabwe | 2020-01-04 | 16320539.0 | NaN | 0.0 | NaN | None | 0.0 | ... | NaN | None | None | None | None | None | None | None | None | None |
| 301597 | ZWE | Africa | Zimbabwe | 2020-01-03 | 16320539.0 | NaN | 0.0 | NaN | None | 0.0 | ... | NaN | None | None | None | None | None | None | None | None | None |
301598 rows × 26 columns
#Querying relevant information
query2 = '''
SELECT location, date, total_cases, new_cases, total_deaths, population
FROM Portfolio..Covid_Deaths
WHERE continent IS NOT NULL
ORDER BY 1, 2 DESC;
'''
df2 = pd.read_sql(query2, engine)
df2
| location | date | total_cases | new_cases | total_deaths | population | |
|---|---|---|---|---|---|---|
| 0 | Afghanistan | 2023-06-07 | 222074.0 | 0.0 | 7918 | 41128772.0 |
| 1 | Afghanistan | 2023-06-06 | 222074.0 | 0.0 | 7918 | 41128772.0 |
| 2 | Afghanistan | 2023-06-05 | 222074.0 | 82.0 | 7918 | 41128772.0 |
| 3 | Afghanistan | 2023-06-04 | 221992.0 | 18.0 | 7918 | 41128772.0 |
| 4 | Afghanistan | 2023-06-03 | 221974.0 | 108.0 | 7918 | 41128772.0 |
| ... | ... | ... | ... | ... | ... | ... |
| 301593 | Zimbabwe | 2020-01-07 | NaN | 0.0 | None | 16320539.0 |
| 301594 | Zimbabwe | 2020-01-06 | NaN | 0.0 | None | 16320539.0 |
| 301595 | Zimbabwe | 2020-01-05 | NaN | 0.0 | None | 16320539.0 |
| 301596 | Zimbabwe | 2020-01-04 | NaN | 0.0 | None | 16320539.0 |
| 301597 | Zimbabwe | 2020-01-03 | NaN | 0.0 | None | 16320539.0 |
301598 rows × 6 columns
The case fatality rate (CFR) is calculated as the ratio between confirmed deaths and confirmed cases.
#Total Cases x Total Deaths in the Philippines
query3 = '''
SELECT location, date, total_cases, total_deaths, (total_deaths/total_cases)*100 AS case_fatality_rate
FROM Portfolio..Covid_Deaths
WHERE location LIKE 'Philippines'
ORDER BY 1, 2 DESC;
'''
df3 = pd.read_sql(query3, engine)
df3.head(10)
| location | date | total_cases | total_deaths | case_fatality_rate | |
|---|---|---|---|---|---|
| 0 | Philippines | 2023-06-07 | 4148401.0 | 66476 | 1.602449 |
| 1 | Philippines | 2023-06-06 | 4148401.0 | 66476 | 1.602449 |
| 2 | Philippines | 2023-06-05 | 4148401.0 | 66476 | 1.602449 |
| 3 | Philippines | 2023-06-04 | 4147129.0 | 66476 | 1.602940 |
| 4 | Philippines | 2023-06-03 | 4145792.0 | 66476 | 1.603457 |
| 5 | Philippines | 2023-06-02 | 4142814.0 | 66466 | 1.604368 |
| 6 | Philippines | 2023-06-01 | 4142814.0 | 66466 | 1.604368 |
| 7 | Philippines | 2023-05-31 | 4141535.0 | 66466 | 1.604864 |
| 8 | Philippines | 2023-05-30 | 4140680.0 | 66466 | 1.605195 |
| 9 | Philippines | 2023-05-29 | 4139295.0 | 66466 | 1.605732 |
The cases_percentage column shows what percentage of population was infected with Covid-19.
#Total Cases x Population in the Philippines
#Shows what percentage of population was infected with Covid
query4 = '''
SELECT location, date, population, total_cases, (total_cases/population)*100 AS cases_percentage
FROM Portfolio..Covid_Deaths
WHERE location LIKE 'Philippines'
ORDER BY 1, 2 DESC;
'''
df4 = pd.read_sql(query4, engine)
df4.head(10)
| location | date | population | total_cases | cases_percentage | |
|---|---|---|---|---|---|
| 0 | Philippines | 2023-06-07 | 115559008.0 | 4148401.0 | 3.589855 |
| 1 | Philippines | 2023-06-06 | 115559008.0 | 4148401.0 | 3.589855 |
| 2 | Philippines | 2023-06-05 | 115559008.0 | 4148401.0 | 3.589855 |
| 3 | Philippines | 2023-06-04 | 115559008.0 | 4147129.0 | 3.588754 |
| 4 | Philippines | 2023-06-03 | 115559008.0 | 4145792.0 | 3.587597 |
| 5 | Philippines | 2023-06-02 | 115559008.0 | 4142814.0 | 3.585020 |
| 6 | Philippines | 2023-06-01 | 115559008.0 | 4142814.0 | 3.585020 |
| 7 | Philippines | 2023-05-31 | 115559008.0 | 4141535.0 | 3.583914 |
| 8 | Philippines | 2023-05-30 | 115559008.0 | 4140680.0 | 3.583174 |
| 9 | Philippines | 2023-05-29 | 115559008.0 | 4139295.0 | 3.581975 |
The cases_percentage column shows what percentage of population was infected with Covid-19 by country sorted from highest to lowest percentage.
#Querying infection rate by country sorted from highest to lowest
query5 = '''
SELECT location, population, MAX(total_cases) AS highest_total_cases, (MAX(total_cases)/population)*100 AS cases_percentage
FROM Portfolio..Covid_Deaths
GROUP BY location, population
ORDER BY cases_percentage DESC;
'''
df5 = pd.read_sql(query5, engine)
df5.head(10)
| location | population | highest_total_cases | cases_percentage | |
|---|---|---|---|---|
| 0 | Cyprus | 896007.0 | 660854.0 | 73.755451 |
| 1 | San Marino | 33690.0 | 24298.0 | 72.122291 |
| 2 | Brunei | 449002.0 | 307686.0 | 68.526644 |
| 3 | Austria | 8939617.0 | 6078070.0 | 67.990273 |
| 4 | Faeroe Islands | 53117.0 | 34658.0 | 65.248414 |
| 5 | Slovenia | 2119843.0 | 1344388.0 | 63.419225 |
| 6 | Gibraltar | 32677.0 | 20550.0 | 62.888270 |
| 7 | Martinique | 367512.0 | 229975.0 | 62.576188 |
| 8 | South Korea | 51815808.0 | 31782676.0 | 61.337799 |
| 9 | Andorra | 79843.0 | 48015.0 | 60.136768 |
#Querying total death count by country sorted from highest to lowest
query6 = '''
SELECT location, MAX(Cast(total_deaths AS int)) AS total_death_count
FROM Portfolio..Covid_Deaths
WHERE continent IS NOT NULL
GROUP BY location
ORDER BY total_death_count DESC;
'''
df6 = pd.read_sql(query6, engine)
df6.head(10)
| location | total_death_count | |
|---|---|---|
| 0 | United States | 1127152.0 |
| 1 | Brazil | 702907.0 |
| 2 | India | 531884.0 |
| 3 | Russia | 399226.0 |
| 4 | Mexico | 334167.0 |
| 5 | United Kingdom | 226645.0 |
| 6 | Peru | 220673.0 |
| 7 | Italy | 190517.0 |
| 8 | Germany | 174412.0 |
| 9 | France | 163680.0 |
#Querying total death count by continent using CTE
query7 = '''
WITH deaths_by_continent
AS
(
SELECT continent, MAX(CAST(total_deaths AS int)) AS total_deaths
FROM Portfolio..Covid_Deaths
WHERE continent IS NOT NULL
GROUP BY continent, location
)
SELECT continent, SUM(total_deaths) AS total_death_count
FROM deaths_by_continent
GROUP BY continent
ORDER BY total_death_count DESC;
'''
df7 = pd.read_sql(query7, engine)
df7
| continent | total_death_count | |
|---|---|---|
| 0 | Europe | 2066566 |
| 1 | Asia | 1633037 |
| 2 | North America | 1601947 |
| 3 | South America | 1356203 |
| 4 | Africa | 258955 |
| 5 | Oceania | 27345 |
#Querying Covid_Vaccinations table
query8 = '''
SELECT iso_code, continent, location, date, new_vaccinations_smoothed
FROM Portfolio..Covid_Vaccinations
WHERE continent IS NOT NULL
ORDER BY 5 DESC;
'''
df8 = pd.read_sql(query8, engine)
df8
| iso_code | continent | location | date | new_vaccinations_smoothed | |
|---|---|---|---|---|---|
| 0 | MYS | Asia | Malaysia | 2021-11-19 | 99992 |
| 1 | BHR | Asia | Bahrain | 2021-12-22 | 9999 |
| 2 | DNK | Europe | Denmark | 2021-10-18 | 9999 |
| 3 | CZE | Europe | Czechia | 2021-09-20 | 9998 |
| 4 | ALB | Europe | Albania | 2021-07-29 | 9998 |
| ... | ... | ... | ... | ... | ... |
| 301593 | BOL | South America | Bolivia | 2020-04-29 | None |
| 301594 | BOL | South America | Bolivia | 2020-04-30 | None |
| 301595 | BOL | South America | Bolivia | 2020-05-01 | None |
| 301596 | BOL | South America | Bolivia | 2020-05-02 | None |
| 301597 | BOL | South America | Bolivia | 2020-05-03 | None |
301598 rows × 5 columns
query9 = '''
SELECT SUM(total_cases) AS total_cases, SUM(country_deaths) AS total_deaths, (SUM(country_deaths)/SUM(total_cases))*100 AS case_fatality_rate,
SUM(max_vax) AS total_people_vaccinated, (SUM(max_vax)/SUM(population))*100 AS vax_rate
FROM
(
SELECT dth.location, dth.population,
MAX(CONVERT(bigint,people_vaccinated)) AS max_vax, SUM(CAST(dth.new_deaths AS float)) AS country_deaths,
SUM(CAST(dth.new_cases AS float)) AS total_cases
FROM Portfolio..Covid_Deaths dth
JOIN Portfolio..Covid_Vaccinations vax
ON dth.location = vax.location AND dth.date = vax.date
WHERE dth.continent IS NOT NULL
GROUP BY dth.location, dth.population
)
AS totalvax
'''
df9 = pd.read_sql(query9, engine)
df9
| total_cases | total_deaths | case_fatality_rate | total_people_vaccinated | vax_rate | |
|---|---|---|---|---|---|
| 0 | 767776899.0 | 6947609.0 | 0.904899 | 5641389568 | 70.120772 |
query10 = '''
SELECT dth.continent, dth.location, dth.date, dth.population, vax.new_people_vaccinated_smoothed AS new_people_vaccinated,
SUM(CONVERT(bigint,vax.new_people_vaccinated_smoothed)) OVER (PARTITION BY dth.location ORDER BY dth.location, dth.date) AS cumulative_people_vaccinated
FROM Portfolio..Covid_Deaths dth
JOIN Portfolio..Covid_Vaccinations vax
ON dth.location = vax.location
AND dth.date = vax.date
WHERE dth.continent IS NOT NULL
ORDER BY cumulative_people_vaccinated DESC;
'''
df10 = pd.read_sql(query10, engine)
df10.head(10)
| continent | location | date | population | new_people_vaccinated | cumulative_people_vaccinated | |
|---|---|---|---|---|---|---|
| 0 | Asia | India | 2023-06-08 | 1.417173e+09 | 217 | 1.027883e+09 |
| 1 | Asia | India | 2023-06-07 | 1.417173e+09 | 218 | 1.027883e+09 |
| 2 | Asia | India | 2023-06-06 | 1.417173e+09 | 217 | 1.027882e+09 |
| 3 | Asia | India | 2023-06-05 | 1.417173e+09 | 112 | 1.027882e+09 |
| 4 | Asia | India | 2023-06-04 | 1.417173e+09 | 116 | 1.027882e+09 |
| 5 | Asia | India | 2023-06-03 | 1.417173e+09 | 130 | 1.027882e+09 |
| 6 | Asia | India | 2023-06-02 | 1.417173e+09 | 133 | 1.027882e+09 |
| 7 | Asia | India | 2023-06-01 | 1.417173e+09 | 135 | 1.027882e+09 |
| 8 | Asia | India | 2023-05-31 | 1.417173e+09 | 135 | 1.027881e+09 |
| 9 | Asia | India | 2023-05-30 | 1.417173e+09 | 134 | 1.027881e+09 |
The percentage of the population that has recieved at least one covid vaccine is calculated using different methods.
#Using Common Table Expression (CTE) to perform calculation on PARTITION BY in previous query
query11 = '''
WITH VaxRate (continent, location, date, population, new_people_vaccinated_smoothed, cumulative_people_vaccinated)
AS
(
SELECT dth.continent, dth.location, dth.date, dth.population, vax.new_people_vaccinated_smoothed AS new_people_vaccinated,
SUM(CONVERT(bigint,vax.new_people_vaccinated_smoothed)) OVER (PARTITION BY dth.location ORDER BY dth.location, dth.date) AS cumulative_people_vaccinated
FROM Portfolio..Covid_Deaths dth
JOIN Portfolio..Covid_Vaccinations vax
ON dth.location = vax.location
AND dth.date = vax.date
WHERE dth.continent IS NOT NULL
)
SELECT *, (cumulative_people_vaccinated/population)*100 AS people_vaccinated_percentage
FROM VaxRate
ORDER BY people_vaccinated_percentage DESC, date DESC;
'''
df11 = pd.read_sql(query11, engine)
df11
| continent | location | date | population | new_people_vaccinated_smoothed | cumulative_people_vaccinated | people_vaccinated_percentage | |
|---|---|---|---|---|---|---|---|
| 0 | Europe | Gibraltar | 2023-06-07 | 32677.0 | None | 44652.0 | 136.646571 |
| 1 | Europe | Gibraltar | 2023-06-06 | 32677.0 | None | 44652.0 | 136.646571 |
| 2 | Europe | Gibraltar | 2023-06-05 | 32677.0 | None | 44652.0 | 136.646571 |
| 3 | Europe | Gibraltar | 2023-06-04 | 32677.0 | None | 44652.0 | 136.646571 |
| 4 | Europe | Gibraltar | 2023-06-03 | 32677.0 | None | 44652.0 | 136.646571 |
| ... | ... | ... | ... | ... | ... | ... | ... |
| 301593 | Africa | Mali | 2020-01-03 | 22593598.0 | None | NaN | NaN |
| 301594 | Asia | Maldives | 2020-01-03 | 523798.0 | None | NaN | NaN |
| 301595 | North America | Martinique | 2020-01-03 | 367512.0 | None | NaN | NaN |
| 301596 | Oceania | Marshall Islands | 2020-01-03 | 41593.0 | None | NaN | NaN |
| 301597 | Europe | Malta | 2020-01-03 | 533293.0 | None | NaN | NaN |
301598 rows × 7 columns
#Using a Temp Table to perform calculation on PARTITION BY in previous query
create1 = '''
CREATE TABLE #VaccinatedPopulationPercentage
(
continent nvarchar(255),
location nvarchar(255),
date datetime,
population numeric,
new_people_vaccinated numeric,
cumulative_people_vaccinated numeric
)
INSERT INTO #VaccinatedPopulationPercentage
SELECT dth.continent, dth.location, dth.date, dth.population, vax.new_people_vaccinated_smoothed AS new_people_vaccinated,
SUM(CONVERT(bigint,vax.new_people_vaccinated_smoothed)) OVER (PARTITION BY dth.location ORDER BY dth.location, dth.date) AS cumulative_people_vaccinated
FROM Portfolio..Covid_Deaths dth
JOIN Portfolio..Covid_Vaccinations vax
ON dth.location = vax.location
AND dth.date = vax.date
WHERE dth.continent IS NOT NULL;
'''
with engine.begin() as connection:
connection.execute(create1)
query12 = '''
SELECT *, (cumulative_people_vaccinated/population)*100 AS people_vaccinated_percentage
FROM #VaccinatedPopulationPercentage
ORDER BY people_vaccinated_percentage DESC, date DESC
DROP TABLE #VaccinatedPopulationPercentage;
'''
df12 = pd.read_sql(query12, engine)
df12
| continent | location | date | population | new_people_vaccinated | cumulative_people_vaccinated | people_vaccinated_percentage | |
|---|---|---|---|---|---|---|---|
| 0 | Europe | Gibraltar | 2023-06-07 | 32677.0 | NaN | 44652.0 | 136.646571 |
| 1 | Europe | Gibraltar | 2023-06-06 | 32677.0 | NaN | 44652.0 | 136.646571 |
| 2 | Europe | Gibraltar | 2023-06-05 | 32677.0 | NaN | 44652.0 | 136.646571 |
| 3 | Europe | Gibraltar | 2023-06-04 | 32677.0 | NaN | 44652.0 | 136.646571 |
| 4 | Europe | Gibraltar | 2023-06-03 | 32677.0 | NaN | 44652.0 | 136.646571 |
| ... | ... | ... | ... | ... | ... | ... | ... |
| 301593 | Africa | Mali | 2020-01-03 | 22593598.0 | NaN | NaN | NaN |
| 301594 | Asia | Maldives | 2020-01-03 | 523798.0 | NaN | NaN | NaN |
| 301595 | North America | Martinique | 2020-01-03 | 367512.0 | NaN | NaN | NaN |
| 301596 | Oceania | Marshall Islands | 2020-01-03 | 41593.0 | NaN | NaN | NaN |
| 301597 | Europe | Malta | 2020-01-03 | 533293.0 | NaN | NaN | NaN |
301598 rows × 7 columns
create2 = '''
CREATE VIEW VaccinatedPopulationPercentage AS
SELECT dth.continent, dth.location, dth.date, dth.population, vax.new_people_vaccinated_smoothed AS new_people_vaccinated,
SUM(CONVERT(bigint,vax.new_people_vaccinated_smoothed)) OVER (PARTITION BY dth.location ORDER BY dth.location, dth.date) AS cumulative_people_vaccinated
FROM Portfolio..Covid_Deaths dth
JOIN Portfolio..Covid_Vaccinations vax
ON dth.location = vax.location
AND dth.date = vax.date
WHERE dth.continent IS NOT NULL
'''
with engine.begin() as connection:
connection.execute(create2)
query13 = '''
SELECT *, (cumulative_people_vaccinated/population)*100 AS people_vaccinated_percentage
FROM VaccinatedPopulationPercentage
ORDER BY people_vaccinated_percentage DESC, date DESC;
'''
df13 = pd.read_sql(query13, engine)
df13
| continent | location | date | population | new_people_vaccinated | cumulative_people_vaccinated | people_vaccinated_percentage | |
|---|---|---|---|---|---|---|---|
| 0 | Europe | Gibraltar | 2023-06-07 | 32677.0 | None | 44652.0 | 136.646571 |
| 1 | Europe | Gibraltar | 2023-06-06 | 32677.0 | None | 44652.0 | 136.646571 |
| 2 | Europe | Gibraltar | 2023-06-05 | 32677.0 | None | 44652.0 | 136.646571 |
| 3 | Europe | Gibraltar | 2023-06-04 | 32677.0 | None | 44652.0 | 136.646571 |
| 4 | Europe | Gibraltar | 2023-06-03 | 32677.0 | None | 44652.0 | 136.646571 |
| ... | ... | ... | ... | ... | ... | ... | ... |
| 301593 | Africa | Mali | 2020-01-03 | 22593598.0 | None | NaN | NaN |
| 301594 | Asia | Maldives | 2020-01-03 | 523798.0 | None | NaN | NaN |
| 301595 | North America | Martinique | 2020-01-03 | 367512.0 | None | NaN | NaN |
| 301596 | Oceania | Marshall Islands | 2020-01-03 | 41593.0 | None | NaN | NaN |
| 301597 | Europe | Malta | 2020-01-03 | 533293.0 | None | NaN | NaN |
301598 rows × 7 columns
#Using CTE to show only max people vaccinated
query14 = '''
WITH MaxVax
AS
(
SELECT dth.location, dth.population,
SUM(CONVERT(bigint,vax.new_people_vaccinated_smoothed)) OVER (PARTITION BY dth.location ORDER BY dth.location, dth.date) AS cumulative_people_vaccinated
FROM Portfolio..Covid_Deaths dth
JOIN Portfolio..Covid_Vaccinations vax
ON dth.location = vax.location AND dth.date = vax.date
WHERE dth.continent IS NOT NULL
)
SELECT location, population, MAX(cumulative_people_vaccinated) AS max_cumulative_people_vaccinated, (MAX(cumulative_people_vaccinated)/population)*100 AS people_vaccinated_percentage
FROM MaxVax
GROUP BY location, population
ORDER BY people_vaccinated_percentage DESC;
'''
df14 = pd.read_sql(query14, engine)
df14.head(10)
| location | population | max_cumulative_people_vaccinated | people_vaccinated_percentage | |
|---|---|---|---|---|
| 0 | Gibraltar | 32677.0 | 44652.0 | 136.646571 |
| 1 | Bhutan | 782457.0 | 959967.0 | 122.686231 |
| 2 | Tokelau | 1893.0 | 2153.0 | 113.734812 |
| 3 | Nauru | 12691.0 | 13356.0 | 105.239934 |
| 4 | Qatar | 2695131.0 | 2788921.0 | 103.479979 |
| 5 | Brunei | 449002.0 | 452685.0 | 100.820264 |
| 6 | United Arab Emirates | 9441138.0 | 9334698.0 | 98.872594 |
| 7 | Cuba | 11212198.0 | 10969191.0 | 97.832655 |
| 8 | Portugal | 10270857.0 | 9815294.0 | 95.564508 |
| 9 | Niue | 1952.0 | 1848.0 | 94.672131 |
Vaccination rate is more than 100% in some countries, the most vaccinated place in the world: Gibraltar for example, also administered vaccinations for guest workers from Spain which is not part of their population.
query15 = '''
WITH CFRVaxRate
AS
(
SELECT dth.continent, dth.location, dth.date, total_cases, total_deaths, (total_deaths/total_cases)*100 AS case_fatality_rate, dth.population, vax.new_people_vaccinated_smoothed AS new_people_vaccinated,
SUM(CONVERT(bigint,vax.new_people_vaccinated_smoothed)) OVER (PARTITION BY dth.location ORDER BY dth.location, dth.date) AS cumulative_people_vaccinated
FROM Portfolio..Covid_Deaths dth
JOIN Portfolio..Covid_Vaccinations vax
ON dth.location = vax.location
AND dth.date = vax.date
WHERE dth.continent IS NOT NULL AND vax.new_people_vaccinated_smoothed > 0 AND dth.location = 'Philippines'
)
SELECT *, (cumulative_people_vaccinated/population)*100 AS people_vaccinated_percentage
FROM CFRVaxRate
ORDER BY 10, 3;
'''
df15 = pd.read_sql(query15, engine)
df15.head(50)
| continent | location | date | total_cases | total_deaths | case_fatality_rate | population | new_people_vaccinated | cumulative_people_vaccinated | people_vaccinated_percentage | |
|---|---|---|---|---|---|---|---|---|---|---|
| 0 | Asia | Philippines | 2021-03-01 | 578375.0 | 12318 | 2.129760 | 115559008.0 | 756 | 756 | 0.000654 |
| 1 | Asia | Philippines | 2021-03-02 | 580440.0 | 12322 | 2.122872 | 115559008.0 | 1396 | 2152 | 0.001862 |
| 2 | Asia | Philippines | 2021-03-03 | 582215.0 | 12369 | 2.124473 | 115559008.0 | 2132 | 4284 | 0.003707 |
| 3 | Asia | Philippines | 2021-03-04 | 584659.0 | 12389 | 2.119013 | 115559008.0 | 2500 | 6784 | 0.005871 |
| 4 | Asia | Philippines | 2021-03-05 | 587699.0 | 12404 | 2.110604 | 115559008.0 | 3514 | 10298 | 0.008911 |
| 5 | Asia | Philippines | 2021-03-06 | 591136.0 | 12423 | 2.101547 | 115559008.0 | 4190 | 14488 | 0.012537 |
| 6 | Asia | Philippines | 2021-03-07 | 594407.0 | 12465 | 2.097048 | 115559008.0 | 4181 | 18669 | 0.016155 |
| 7 | Asia | Philippines | 2021-03-08 | 597760.0 | 12516 | 2.093817 | 115559008.0 | 6178 | 24847 | 0.021502 |
| 8 | Asia | Philippines | 2021-03-09 | 600422.0 | 12521 | 2.085367 | 115559008.0 | 12326 | 37173 | 0.032168 |
| 9 | Asia | Philippines | 2021-03-10 | 603299.0 | 12528 | 2.076582 | 115559008.0 | 15443 | 52616 | 0.045532 |
| 10 | Asia | Philippines | 2021-03-11 | 607040.0 | 12545 | 2.066585 | 115559008.0 | 18690 | 71306 | 0.061705 |
| 11 | Asia | Philippines | 2021-03-12 | 611611.0 | 12608 | 2.061441 | 115559008.0 | 21370 | 92676 | 0.080198 |
| 12 | Asia | Philippines | 2021-03-13 | 616599.0 | 12694 | 2.058712 | 115559008.0 | 24051 | 116727 | 0.101011 |
| 13 | Asia | Philippines | 2021-03-14 | 621489.0 | 12766 | 2.054099 | 115559008.0 | 25068 | 141795 | 0.122704 |
| 14 | Asia | Philippines | 2021-03-15 | 626883.0 | 12829 | 2.046474 | 115559008.0 | 24571 | 166366 | 0.143966 |
| 15 | Asia | Philippines | 2021-03-16 | 631311.0 | 12837 | 2.033388 | 115559008.0 | 18245 | 184611 | 0.159755 |
| 16 | Asia | Philippines | 2021-03-17 | 635694.0 | 12848 | 2.021098 | 115559008.0 | 22155 | 206766 | 0.178927 |
| 17 | Asia | Philippines | 2021-03-18 | 640963.0 | 12866 | 2.007292 | 115559008.0 | 21587 | 228353 | 0.197607 |
| 18 | Asia | Philippines | 2021-03-19 | 648057.0 | 12887 | 1.988560 | 115559008.0 | 21020 | 249373 | 0.215797 |
| 19 | Asia | Philippines | 2021-03-20 | 656037.0 | 12900 | 1.966353 | 115559008.0 | 20452 | 269825 | 0.233495 |
| 20 | Asia | Philippines | 2021-03-21 | 663773.0 | 12930 | 1.947955 | 115559008.0 | 27020 | 296845 | 0.256877 |
| 21 | Asia | Philippines | 2021-03-22 | 671786.0 | 12968 | 1.930377 | 115559008.0 | 33587 | 330432 | 0.285942 |
| 22 | Asia | Philippines | 2021-03-23 | 677647.0 | 12972 | 1.914271 | 115559008.0 | 41648 | 372080 | 0.321983 |
| 23 | Asia | Philippines | 2021-03-24 | 684275.0 | 12992 | 1.898652 | 115559008.0 | 39393 | 411473 | 0.356072 |
| 24 | Asia | Philippines | 2021-03-25 | 693018.0 | 13039 | 1.881481 | 115559008.0 | 41484 | 452957 | 0.391970 |
| 25 | Asia | Philippines | 2021-03-26 | 702847.0 | 13095 | 1.863137 | 115559008.0 | 43576 | 496533 | 0.429679 |
| 26 | Asia | Philippines | 2021-03-27 | 712417.0 | 13149 | 1.845689 | 115559008.0 | 45668 | 542201 | 0.469198 |
| 27 | Asia | Philippines | 2021-03-28 | 721878.0 | 13159 | 1.822884 | 115559008.0 | 41361 | 583562 | 0.504990 |
| 28 | Asia | Philippines | 2021-03-29 | 731885.0 | 13170 | 1.799463 | 115559008.0 | 37055 | 620617 | 0.537056 |
| 29 | Asia | Philippines | 2021-03-30 | 741160.0 | 13186 | 1.779103 | 115559008.0 | 32748 | 653365 | 0.565395 |
| 30 | Asia | Philippines | 2021-03-31 | 747267.0 | 13191 | 1.765233 | 115559008.0 | 29525 | 682890 | 0.590945 |
| 31 | Asia | Philippines | 2021-04-01 | 756187.0 | 13297 | 1.758427 | 115559008.0 | 26302 | 709192 | 0.613706 |
| 32 | Asia | Philippines | 2021-04-02 | 771467.0 | 13303 | 1.724377 | 115559008.0 | 23079 | 732271 | 0.633677 |
| 33 | Asia | Philippines | 2021-04-03 | 784023.0 | 13320 | 1.698930 | 115559008.0 | 19856 | 752127 | 0.650860 |
| 34 | Asia | Philippines | 2021-04-04 | 795043.0 | 13423 | 1.688336 | 115559008.0 | 18222 | 770349 | 0.666628 |
| 35 | Asia | Philippines | 2021-04-05 | 803387.0 | 13425 | 1.671050 | 115559008.0 | 16588 | 786937 | 0.680983 |
| 36 | Asia | Philippines | 2021-04-06 | 812750.0 | 13435 | 1.653030 | 115559008.0 | 19235 | 806172 | 0.697628 |
| 37 | Asia | Philippines | 2021-04-07 | 819150.0 | 13817 | 1.686748 | 115559008.0 | 21034 | 827206 | 0.715830 |
| 38 | Asia | Philippines | 2021-04-08 | 828329.0 | 14059 | 1.697272 | 115559008.0 | 22832 | 850038 | 0.735588 |
| 39 | Asia | Philippines | 2021-04-09 | 840535.0 | 14119 | 1.679763 | 115559008.0 | 24631 | 874669 | 0.756902 |
| 40 | Asia | Philippines | 2021-04-10 | 853187.0 | 14520 | 1.701854 | 115559008.0 | 26430 | 901099 | 0.779774 |
| 41 | Asia | Philippines | 2021-04-11 | 864847.0 | 14744 | 1.704810 | 115559008.0 | 28056 | 929155 | 0.804052 |
| 42 | Asia | Philippines | 2021-04-12 | 876212.0 | 14945 | 1.705637 | 115559008.0 | 32378 | 961533 | 0.832071 |
| 43 | Asia | Philippines | 2021-04-13 | 884758.0 | 15149 | 1.712220 | 115559008.0 | 31634 | 993167 | 0.859446 |
| 44 | Asia | Philippines | 2021-04-14 | 892856.0 | 15286 | 1.712034 | 115559008.0 | 33886 | 1027053 | 0.888769 |
| 45 | Asia | Philippines | 2021-04-15 | 904245.0 | 15447 | 1.708276 | 115559008.0 | 36137 | 1063190 | 0.920041 |
| 46 | Asia | Philippines | 2021-04-16 | 914951.0 | 15594 | 1.704354 | 115559008.0 | 38389 | 1101579 | 0.953261 |
| 47 | Asia | Philippines | 2021-04-17 | 926035.0 | 15738 | 1.699504 | 115559008.0 | 40641 | 1142220 | 0.988430 |
| 48 | Asia | Philippines | 2021-04-18 | 936117.0 | 15810 | 1.688891 | 115559008.0 | 38838 | 1181058 | 1.022039 |
| 49 | Asia | Philippines | 2021-04-19 | 945727.0 | 15960 | 1.687591 | 115559008.0 | 37558 | 1218616 | 1.054540 |
In the query above, it can be observed that as the vaccination rate increases, the case fatality rate decreases. This suggests that COVID-19 vaccination is effective towards combatting the severe effects of the desease. However, there are a lot more factors to consider that may affect the case fatality rate such as the vaccine brands, COVID-19 variants, demographic and socioeconomic factors, and healthcare capacity and access.